﻿<%@ Page Language="C#" AutoEventWireup="true" CodeFile="r_comercial_geral_x2.aspx.cs" Inherits="relatorios_r_comercial_geral_x2" %>

<%
    string Permissao_RelatoriosFinanceirosComercial = "" + this.Session["Permissao_RelatoriosFinanceirosComercial"];
    if (Permissao_RelatoriosFinanceirosComercial != "1")
    {
        this.Response.Redirect("../login.aspx");
    }    
%>

<div style="background-color: White;">
    <%
        bool hasResults = false;
        DateTime now = DateTime.Now.AddMonths(-1);
        DateTime now2 = DateTime.Now;
        
        // FIRST DATE
        string month = this.Request["month"];
        string year = this.Request["year"];            
        int iMonth = -1;
        int iYear = -1;            
        int maxDay = 31;
            
        // Month
        if (string.IsNullOrEmpty(month) == true)
        {
            iMonth = now.Month;
        }
        else
        {
            try
            {
                iMonth = int.Parse(month);
            }
            catch { iMonth = now.Month; }
        }
        // Year            
        if (string.IsNullOrEmpty(year) == true)
        {
            iYear = now.Year;
        }
        else
        {
            try
            {
                iYear = int.Parse(year);
            }
            catch { iYear = now.Year; }                
        }
        maxDay = DateTime.DaysInMonth(iYear, iMonth);

        // SECOND DATE
        string month2 = this.Request["month2"];
        string year2 = this.Request["year2"];
        int iMonth2 = -1;
        int iYear2 = -1;
        int maxDay2 = 31;

        // Month
        if (string.IsNullOrEmpty(month2) == true)
        {
            iMonth2 = now2.Month;
        }
        else
        {
            try
            {
                iMonth2 = int.Parse(month2);
            }
            catch { iMonth2 = now2.Month; }
        }
        // Year            
        if (string.IsNullOrEmpty(year2) == true)
        {
            iYear2 = now2.Year;
        }
        else
        {
            try
            {
                iYear2 = int.Parse(year2);
            }
            catch { iYear2 = now2.Year; }
        }
        maxDay2 = DateTime.DaysInMonth(iYear2, iMonth2);                              
    %>
    <center>
        <form>
            <table>
                <tr>
                    <td>Mês</td>
                    <td>Ano</td>
                    <td></td>
                </tr>

                <tr>
                    <td>
                        <input type="text" id="month" name="month" value="<%= iMonth %>" />
                        <br />
                        <br />
                        <input type="text" id="month2" name="month2" value="<%= iMonth2 %>" />
                    </td>
                    <td>
                        <input type="text" id="year" name="year" value="<%= iYear %>" />
                        <br />
                        <br />
                        <input type="text" id="year2" name="year2" value="<%= iYear2 %>" />
                    </td>
                    <td>
                        <br />
                        <br />
                        <input type="submit" value="ok" />
                    </td>
                </tr>
            </table>
        </form>
    </center>
          
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Dia', 'Data: <%= iMonth + "/" + iYear %>', 'Data: <%= iMonth2 + "/" + iYear2 %>'],
                <%
                    hasResults = false;
                    PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
                    PL.DataBase.DAO.Instance.Connector.Open();
    
                    string message = "";
                    try
                    {                           
                        // First Value                             
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                        DateTime targetDate2 = new DateTime(iYear2, iMonth2, 1);   
                        DateTime maxDate2 = new DateTime(iYear2, iMonth2, maxDay2);
                        string sql = "";

                        // Value 1                        
                        sql += "";
                        sql += "SELECT        Vendedor, SUM(Total) AS tt, ";
                        sql += "                             (SELECT        SUM(Total) AS tt2 ";
                        sql += "                               FROM            BI_Atendimentos_Comerciais AS BI_Atendimentos_Comerciais_2 ";
                        sql += "                               WHERE        (Data >= CONVERT(VARCHAR, '" + targetDate.ToString().Split(' ')[0] + "', 103)) AND (Data <= CONVERT(VARCHAR, '" + maxDate.ToString().Split(' ')[0] + "', 103)) AND  ";
                        sql += "                                                         (Vendedor = BI_Atendimentos_Comerciais.Vendedor) ";
                        sql += "                               GROUP BY Vendedor) AS tt2 ";
                        sql += "FROM            BI_Atendimentos_Comerciais ";
                        sql += "WHERE        (Data >= CONVERT(VARCHAR, '" + targetDate2.ToString().Split(' ')[0] + "', 103)) AND (Data <= CONVERT(VARCHAR, '" + maxDate2.ToString().Split(' ')[0] + "', 103)) ";
                        sql += "GROUP BY Vendedor ";
                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {     
                            hasResults = true;
                            
                            string tt1 = "" + reader["tt"];
                            string tt2 = "" + reader["tt2"];

                            if (string.IsNullOrEmpty(tt1) == true)
                            {
                                tt1 = "0";
                            }
                            if (string.IsNullOrEmpty(tt2) == true)
                            {
                                tt2 = "0";
                            }
                            this.Response.Write("['" + reader["Vendedor"] + "', " + tt1 + ", " + tt2 + "],");
                        }
                        reader.Close();                                                                                                
                    }
                    catch (Exception e)
                    {
                        message = e.Message;
                    }

                    PL.DataBase.DAO.Instance.Connector.Close();
                    this.Response.Write(message);
                %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Atendimentos no mês'
                    };

                    var chart = new google.visualization.LineChart(document.getElementById('r_comercial_atendimentos'));

                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_comercial_atendimentos" style="width: 80%; height: 200px;">
        <center>Atendimentos no mes: nenhum dado encontrado</center>
    </div>

    <!-- ------- -->

    <br />
    <br />

    <!-- ------- -->
    
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Colaborador', 'Data: <%= iMonth + "/" + iYear %>', 'Data: <%= iMonth2 + "/" + iYear2 %>'],
                <%
                    hasResults = false;
                    PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
                    PL.DataBase.DAO.Instance.Connector.Open();
                        
                    try
                    {   
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                        DateTime targetDate2 = new DateTime(iYear2, iMonth2, 1);   
                        DateTime maxDate2 = new DateTime(iYear2, iMonth2, maxDay2);
                                                
                        string sql = "";                        

                        sql += " SELECT        D0S001Vendedor, SUM(F0S203Mensalidade) AS Total, ";
                        sql += "                              (SELECT        SUM(F0S203Mensalidade) AS Total2 ";
                        sql += "                                FROM            BI_CP_Orcamentos AS BI_CP_Orcamentos_2 ";
                        sql += "                                WHERE        (D2N009Data >= CONVERT(VARCHAR, '" + targetDate.ToString().Split(' ')[0] + "', 103)) AND (D2N009Data <= CONVERT(VARCHAR, '" + maxDate.ToString().Split(' ')[0] + "', 103)) AND (D2N010Situacao = '3') AND (F0S203Mensalidade IS NOT NULL) AND  ";
                        sql += "                                                          (D0S001Vendedor = BI_CP_Orcamentos.D0S001Vendedor) ";
                        sql += "                                GROUP BY D0S001Vendedor) AS Total2 ";
                        sql += " FROM            BI_CP_Orcamentos ";
                        sql += " WHERE        (D2N009Data >= CONVERT(VARCHAR, '" + targetDate2.ToString().Split(' ')[0] + "', 103)) AND (D2N009Data <= CONVERT(VARCHAR, '" + maxDate2.ToString().Split(' ')[0] + "', 103)) AND (D2N010Situacao = '3') AND (F0S203Mensalidade IS NOT NULL) ";
                        sql += " GROUP BY D0S001Vendedor ";
                        
                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {                                    
                            string solution = "" + reader["D0S001Vendedor"];
                            string total = "" + reader["Total"];
                            string total2 = "" + reader["Total2"];
                            if (string.IsNullOrEmpty(solution) == false && (total != "0,00" || total2 != "0,00"))
                            {
                                if (string.IsNullOrEmpty(total) == true)
                                {
                                    total = "0";
                                }
                                if (string.IsNullOrEmpty(total2) == true)
                                {
                                    total2 = "0";
                                }
                                hasResults = true;    
                                this.Response.Write("['" + solution + "', " + total.Replace(",", ".")+ ", " + total2.Replace(",", ".") + "],");
                            }
                        }
                        reader.Close();                        
                    }
                    catch (Exception e)
                    {
                        message = e.Message;
                    }

                    PL.DataBase.DAO.Instance.Connector.Close();                    
                    this.Response.Write(message);
                %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Mensalidades adquiridas no mês por colaborador'
                    };

                    var chart = new google.visualization.LineChart(document.getElementById('r_mensalidade_comercial_por_colaborador'));

                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_mensalidade_comercial_por_colaborador" style="width: 80%; height: 200px;">
        <center>Mensalidade por coloborador: nenhum dado encontrado</center>
    </div>

    <!-- ------- -->

    <br />
    <br />

    <!-- ------- -->

    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Colaborador', 'Data: <%= iMonth + "/" + iYear %>', 'Data: <%= iMonth2 + "/" + iYear2 %>'],
                <%
                    hasResults = false;
                    PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
                    PL.DataBase.DAO.Instance.Connector.Open();
                        
                    try
                    {   
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                        DateTime targetDate2 = new DateTime(iYear2, iMonth2, 1);   
                        DateTime maxDate2 = new DateTime(iYear2, iMonth2, maxDay2);                                            
                                                
                        string sql = "";                        
                        sql += " SELECT        D0S001Vendedor, SUM(F0S202Valor) AS Total, ";
                        sql += "                              (SELECT        SUM(F0S202Valor) AS Total2 ";
                        sql += "                                FROM            BI_CP_Orcamentos AS BI_CP_Orcamentos_2 ";
                        sql += "                                WHERE        (D2N009Data >= CONVERT(VARCHAR, '" + targetDate.ToString().Split(' ')[0] + "', 103)) AND (D2N009Data <= CONVERT(VARCHAR, '" + maxDate.ToString().Split(' ')[0] + "', 103)) AND (D2N010Situacao = '3') AND (F0S202Valor IS NOT NULL) AND  ";
                        sql += "                                                          (D0S001Vendedor = BI_CP_Orcamentos.D0S001Vendedor) ";
                        sql += "                                GROUP BY D0S001Vendedor) AS Total2 ";
                        sql += " FROM            BI_CP_Orcamentos ";
                        sql += " WHERE        (D2N009Data >= CONVERT(VARCHAR, '" + targetDate2.ToString().Split(' ')[0] + "', 103)) AND (D2N009Data <= CONVERT(VARCHAR, '" + maxDate2.ToString().Split(' ')[0] + "', 103)) AND (D2N010Situacao = '3') AND (F0S202Valor IS NOT NULL) ";
                        sql += " GROUP BY D0S001Vendedor ";
                        
                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {                                       
                            string solution = "" + reader["D0S001Vendedor"];
                            string total = "" + reader["Total"];
                            string total2 = "" + reader["Total2"];
                            if (string.IsNullOrEmpty(solution) == false && (total != "0,00" || total2 != "0,00"))
                            {
                                if (string.IsNullOrEmpty(total) == true)
                                {
                                    total = "0";
                                }
                                if (string.IsNullOrEmpty(total2) == true)
                                {
                                    total2 = "0";
                                }
                                hasResults = true;    
                                this.Response.Write("['" + solution + "', " + total.Replace(",", ".")+ ", " + total2.Replace(",", ".") + "],");
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception e)
                    {
                        message = e.Message;
                    }

                    PL.DataBase.DAO.Instance.Connector.Close();
                    this.Response.Write(message);
                %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Tx adquiridas no mês por colaborador'
                    };

                    var chart = new google.visualization.LineChart(document.getElementById('r_tx_comercial_por_colaborador'));

                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_tx_comercial_por_colaborador" style="width: 80%; height: 200px;">
        <center>Tx por colaborador: nenhum dado encontrado</center>
    </div>

    <!-- ------- -->
</div>


<!--
                        // First Value     
                        List<PL.Models.KeyDoubleValue> chartValues = new List<PL.Models.KeyDoubleValue>();
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                        string sql = "";

                        // Value 1
                        sql += "SELECT Vendedor, SUM(Total) As tt From BI_Atendimentos_Comerciais ";
                        sql += " WHERE ";                    
                        sql += " Data >= CONVERT(VARCHAR, '" + targetDate.ToString().Split(' ')[0] + "',103) ";
                        sql += " AND Data <= CONVERT(VARCHAR, '" + maxDate.ToString().Split(' ')[0] + "',103) ";
                        sql += " GROUP BY Vendedor ";                        
                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {     
                            hasResults = true;

                            PL.Models.KeyDoubleValue currentValue = new PL.Models.KeyDoubleValue();
                            currentValue.Key = "" + reader["Vendedor"];
                            currentValue.Value1 = "" + reader["tt"];
                            chartValues.Add(currentValue);
                            
                            //this.Response.Write("['" + reader["Vendedor"] + "', " + reader["tt"] + ", " + "100" + "],");
                        }
                        reader.Close();  
                        
                        // Value 2
                        targetDate = new DateTime(iYear2, iMonth2, 1);   
                        maxDate = new DateTime(iYear2, iMonth2, maxDay2);
                        sql = "SELECT Vendedor, SUM(Total) As tt From BI_Atendimentos_Comerciais ";
                        sql += " WHERE ";                    
                        sql += " Data >= CONVERT(VARCHAR, '" + targetDate.ToString().Split(' ')[0] + "',103) ";
                        sql += " AND Data <= CONVERT(VARCHAR, '" + maxDate.ToString().Split(' ')[0] + "',103) ";
                        sql += " GROUP BY Vendedor ";                        
                        reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {     
                            hasResults = true;

                            PL.Models.KeyDoubleValue currentValue = new PL.Models.KeyDoubleValue();
                            currentValue.Key = "" + reader["Vendedor"];

                            bool found = false;
                            foreach (var old in chartValues) 
                            {
                                if (old.Key == currentValue.Key)
                                {
                                    found = true;
                                    old.Value2 = "" + reader["tt"];
                                    break;
                                }
                            }
                            currentValue.Value2 = "" + reader["tt"];

                            if (found == false)
                            {
                                chartValues.Add(currentValue);
                            }                            
                            //this.Response.Write("['" + reader["Vendedor"] + "', " + reader["tt"] + ", " + "100" + "],");
                        }
                        reader.Close();   
                        
                        foreach (var final in chartValues) 
                        {                            
                            if (string.IsNullOrEmpty(final.Value1) == true)
                            {
                                final.Value1 = "0";
                            }
                            if (string.IsNullOrEmpty(final.Value2) == true)
                            {
                                final.Value2 = "0";
                            }
                            this.Response.Write("['" + final.Key + "', " + final.Value1 + ", " + final.Value2 + "],");
                        } 
-->